package com.kehongborui.fliggy.util.sqlite;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.jdbc.Sql;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * sqlite数据库相关
 *
 * @author zhang peng yang ming
 * @create 2018-09-13 22:03
 */
public class SqliteHelper {
    private static SqliteHelper instance;
    private String dbName;

    public static SqliteHelper getInstance(String dbName) {
        if (instance == null) {
            synchronized (SqliteHelper.class) {
                if (instance == null) {
                    instance = new SqliteHelper(dbName);
                }
            }
        }

        return instance;
    }

    private SqliteHelper(String dbName) {
        this.dbName = dbName;
    }
    /**
     * 获取数据库链接
     *
     * @param dbName
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConn(String dbName) throws ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + dbName);
        return connection;
    }


    public <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws SQLException, ClassNotFoundException {
        List<T> rsList = new ArrayList<T>();
        Connection conn = null;
        Statement statement = null;
        try {
            conn = getConn(dbName); // 获取链接
            statement = conn.createStatement();
            ResultSet set = statement.executeQuery(sql);
            while (set.next()) {
                rsList.add(rm.mapRow(set, set.getRow()));
            }
        } finally {
            close(conn, statement);
        }

        return rsList;
    }

    public int executeUpdate(String sql) {
        Connection conn = null;
        Statement statement = null;

        try {
            conn = getConn(dbName);
            statement = conn.createStatement();
            int i = statement.executeUpdate(sql);
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, statement);
            return -1;
        }
    }

    public void close(Connection conn, Statement state) {
        try {
            if (state != null) {
                state.close();
            }

            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
